﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml.Packaging;
using System.Xml.Linq;
using System.Xml;
using System.Data;
using System.Diagnostics;
using OpenXmlPowerTools;

namespace DbWord
{
    class ChartHelper
    {
        private XmlNodeList GetChartNode()
        {
            XmlDocument doc = new XmlDocument();
            doc.Load(Defs.XmlDocFile);

            XmlNodeList nodelist = doc.SelectNodes(Defs.Xml_ChartsPath + "//Chart");
            return nodelist;
        }
        public void UpdateChart(string file)
        {
            XmlNodeList nodelist = GetChartNode();

            foreach (XmlNode node in nodelist)
            {
                ChartArgs configChart = GetConfigChart(node);
                try
                {
                    var chart4Data = BuildData(configChart);

                    using (var wDoc = WordprocessingDocument.Open(file, true))
                    {
                        if (configChart.Type == ChartArgsType.Default)
                            ChartUpdater.UpdateChartAuto(wDoc, configChart.Name, chart4Data);
                        if (configChart.Type == ChartArgsType.Manual)
                            ChartUpdater.UpdateChart(wDoc, configChart.Name, chart4Data);
                    }// end for xmlnode
                }
                catch (Exception ex)
                {
                    Trace.WriteLine(string.Concat(configChart.Name, " => ", ex.Message));
                }
            }
        }

        private ChartData BuildData(ChartArgs configChart)
        {
            // Get Data
            System.Data.DataTable table = new Request.Helper().Idb.FillTable(configChart.Sql);

            var columns = (from r in table.AsEnumerable()
                           select r[configChart.xAxis].ToString()).Distinct().ToList();
            var series = (from r in table.AsEnumerable()
                          select r[configChart.Series].ToString()).Distinct().ToArray();

            double[][] value = new double[series.Length][];
            for (int i = 0; i < series.Length; i++)
            {
                var rs = (from r in table.AsEnumerable()
                          where r[configChart.Series].ToString() == series[i]
                          select r).ToArray();

                var dic = rs.Cast<DataRow>().ToDictionary(
                    r => r[configChart.xAxis],
                    r => r[configChart.yAxis]);

                int maxcount = columns.IndexOf(dic.Last().Key.ToString());
                value[i] = new double[columns.Count].Select(o => double.MinValue).ToArray();
                double[] vals = dic.Values.Select(o =>
                    string.IsNullOrEmpty(o.ToString()) ? double.MinValue : Convert.ToDouble(o)).ToArray();

                foreach (KeyValuePair<object, object> kvp in dic)
                {
                    value[i][columns.IndexOf(kvp.Key.ToString())] = string.IsNullOrEmpty(kvp.Value.ToString()) ?
                        double.MinValue : Convert.ToDouble(kvp.Value);
                }
            }

            var chart4Data = new ChartData
            {
                SeriesNames = series,
                CategoryNames = columns.ToArray(),
                Values = value
            };
            return chart4Data;
        }

        private string ToExcelInteger(DateTime dateTime)
        {
            var str = ((dateTime - new DateTime(1900, 1, 1)).TotalDays + 2).ToString();
            return str;
        }

        /// <summary>
        /// 给Chart 用, word中无法给chart做标记
        /// </summary>
        /// <param name="index"></param>
        /// <returns></returns>
        private ChartArgs GetConfigChart(XmlNode node)
        {
            ChartArgs chart = new ChartArgs()
            {
                Name = node.SelectSingleNode("Name").InnerText.Trim(),
                Type = (ChartArgsType)Enum.Parse(typeof(ChartArgsType), node.Attributes["type"].InnerText),
                Series = node.SelectSingleNode("Series").InnerText.Trim(),
                Sql = node.SelectSingleNode("Sql").InnerText.Trim(),
                xAxis = node.SelectSingleNode("xAxis").InnerText.Trim(),
                yAxis = node.SelectSingleNode("yAxis").InnerText.Trim()
            };
            string sql = node.SelectSingleNode("Sql").InnerText;
            chart.Sql = string.Format(sql, "");
            return chart;
        }
    }

    public class ChartData
    {
        public string[] SeriesNames;
        public string[] CategoryNames;
        public double[][] Values;
    }


    public class ChartUpdater
    {
        public static bool UpdateChart(WordprocessingDocument wDoc, string contentControlTag, ChartData chartData)
        {
            var mainDocumentPart = wDoc.MainDocumentPart;
            var mdXDoc = mainDocumentPart.GetXDocument();
            var cc = mdXDoc.Descendants(W.sdt)
                .FirstOrDefault(sdt => (string)sdt.Elements(W.sdtPr).Elements(W.tag).Attributes(W.val).FirstOrDefault() == contentControlTag);
            if (cc != null)
            {
                var chartRid = (string)cc.Descendants(C.chart).Attributes(R.id).FirstOrDefault();
                if (chartRid != null)
                {
                    ChartPart chartPart = (ChartPart)mainDocumentPart.GetPartById(chartRid);
                    UpdateChart(chartPart, chartData);
                    var newContent = cc.Elements(W.sdtContent).Elements().Select(e => new XElement(e));
                    cc.ReplaceWith(newContent);
                    mainDocumentPart.PutXDocument();
                    Trace.WriteLine(string.Concat("Success => ", contentControlTag));
                    return true;
                }
            }
            Trace.WriteLine("Fail => " + contentControlTag);
            return false;
        }
        public static bool UpdateChartAuto(WordprocessingDocument wDoc, string contentControlTag, ChartData chartData)
        {
            var mainDocumentPart = wDoc.MainDocumentPart;
            var mdXDoc = mainDocumentPart.GetXDocument();
            var cc = mdXDoc.Descendants(W.sdt)
                .FirstOrDefault(sdt => (string)sdt.Elements(W.sdtPr).Elements(W.tag).Attributes(W.val).FirstOrDefault() == contentControlTag);
            if (cc != null)
            {
                var chartRid = (string)cc.Descendants(C.chart).Attributes(R.id).FirstOrDefault();
                if (chartRid != null)
                {
                    ChartPart chartPart = (ChartPart)mainDocumentPart.GetPartById(chartRid);
                    UpdateChartAuto(chartPart, chartData);
                    var newContent = cc.Elements(W.sdtContent).Elements().Select(e => new XElement(e));
                    cc.ReplaceWith(newContent);
                    mainDocumentPart.PutXDocument();
                    Trace.WriteLine(string.Concat("Success => ", contentControlTag));
                    return true;
                }
            }
            Trace.WriteLine("Fail => " + contentControlTag);
            return false;
        }

        private static void UpdateChart(ChartPart chartPart, ChartData chartData)
        {
            if (chartData.Values.Length != chartData.SeriesNames.Length)
                throw new ArgumentException("Invalid chart data");

            UnlinkSpreadsheet(chartPart);
            UpdateSeries(chartPart, chartData);
        }

        private static void UpdateChartAuto(ChartPart chartPart, ChartData chartData)
        {
            if (chartData.Values.Length != chartData.SeriesNames.Length)
                throw new ArgumentException("Invalid chart data");
            foreach (var ser in chartData.Values)
            {
                if (ser.Length != chartData.CategoryNames.Length)
                    throw new ArgumentException("Invalid chart data");
            }

            UnlinkSpreadsheet(chartPart);
            UpdateSeriesAuto(chartPart, chartData);
        }

        private static void UnlinkSpreadsheet(ChartPart chartPart)
        {
            XDocument cpx = chartPart.GetXDocument();
            XElement newRoot = (XElement)UnlinkSpreadsheetTransform(cpx.Root);
            cpx.Root.ReplaceWith(newRoot);
            chartPart.PutXDocument();
            var workbookPartPair = chartPart.Parts.FirstOrDefault(p => p.OpenXmlPart.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            if (workbookPartPair != null)
                chartPart.DeletePart(workbookPartPair.OpenXmlPart);
        }

        /// <summary>
        /// 本方法要求word中事先做好series，主要和次要纵坐标绑定数据。 
        /// </summary>
        /// <param name="chartPart"></param>
        /// <param name="chartData"></param>
        private static void UpdateSeries(ChartPart chartPart, ChartData chartData)
        {
            XDocument cpXDoc = chartPart.GetXDocument();
            XElement root = cpXDoc.Root;

            XElement[] serList = root.Descendants(C.ser).ToArray();
            for (int i = 0; i < serList.Count(); i++)
            {
                var series = serList[i];
                var seriesname = series.Element(C.tx).Value;
                var numLitIndex = Array.IndexOf(chartData.SeriesNames, seriesname);

                #region replace cat
                var cat1 = series.Elements(C.cat).FirstOrDefault();
                if (series.Elements(C.cat).Elements(C.numLit).Any())
                {
                    var newcat = new XElement(C.cat,
                        new XElement(C.numLit,
                        series.Elements(C.cat).Elements(C.numLit).Elements(C.formatCode),
                        new XElement(C.ptCount, new XAttribute("val", chartData.CategoryNames.Length)),
                        chartData.CategoryNames.Select((string cn, int ci) =>
                        {
                            var newPt = new XElement(C.pt,
                                new XAttribute("idx", ci),
                                new XElement(C.v,
                                    Int32.Parse(chartData.CategoryNames[ci]).ToString()));  
                            // convert to int and back to string
                            // to make sure that the cat names are integer values, i.e. dates
                            return newPt;
                        })));
                    cat1.ReplaceWith(newcat);
                }
                else
                {
                    var newcat = new XElement(C.cat,
                        new XElement(C.strLit,
                        new XElement(C.ptCount, new XAttribute("val", chartData.CategoryNames.Length)),
                        chartData.CategoryNames.Select((string cn, int ci) =>
                        {
                            var newPt = new XElement(C.pt,
                                new XAttribute("idx", ci),
                                new XElement(C.v, chartData.CategoryNames[ci]));
                            return newPt;
                        })));
                    cat1.ReplaceWith(newcat);
                }
                #endregion replace cat

                var numLit = series.Elements(C.val).Elements(C.numLit).FirstOrDefault();
                var newnumLit = new XElement(C.val,
                                new XElement(C.numLit,
                                    numLit.Elements(C.formatCode),
                                    new XElement(C.ptCount, new XAttribute("val", chartData.CategoryNames.Length)),
                                    chartData.CategoryNames.Select((string cn, int ci) =>
                                    {
                                        if (ci > chartData.Values[numLitIndex].Length - 1)
                                            return null;
                                        if (chartData.Values[numLitIndex][ci] == double.MinValue)
                                            return null;
                                        var newPt = new XElement(C.pt,
                                            new XAttribute("idx", ci),
                                            new XElement(C.v, ci < chartData.Values[numLitIndex].Length ?
                                                (object)chartData.Values[numLitIndex][ci] : null));
                                        return newPt;
                                    })));
                numLit.ReplaceWith(newnumLit);
            }// end for C.ser
            chartPart.PutXDocument();
        }

        private static void UpdateSeriesAuto(ChartPart chartPart, ChartData chartData)
        {
            XDocument cpXDoc = chartPart.GetXDocument();
            XElement root = cpXDoc.Root;
            var firstSeries = root.Descendants(C.ser).FirstOrDefault();
            var numLit = firstSeries.Elements(C.val).Elements(C.numLit).FirstOrDefault();

            // remove all but first series
            firstSeries.Parent.Elements(C.ser).Skip(1).Remove();

            var newSetOfSeries = chartData.SeriesNames
                .Select((string sn, int si) =>
                {
                    XElement cat = null;

                    if (firstSeries.Elements(C.cat).Elements(C.numLit).Any())
                    {
                        cat = new XElement(C.cat,
                            new XElement(C.numLit,
                                firstSeries.Elements(C.cat).Elements(C.numLit).Elements(C.formatCode),
                                new XElement(C.ptCount, new XAttribute("val", chartData.CategoryNames.Length)),
                                chartData.CategoryNames.Select((string cn, int ci) =>
                                {
                                    var newPt = new XElement(C.pt,
                                        new XAttribute("idx", ci),
                                        new XElement(C.v,
                                            Int32.Parse(chartData.CategoryNames[ci]).ToString()));  // convert to int and back to string
                                    // to make sure that the cat names are integer values, i.e. dates
                                    return newPt;
                                })));
                    }
                    else
                    {
                        cat = new XElement(C.cat,
                            new XElement(C.strLit,
                                new XElement(C.ptCount, new XAttribute("val", chartData.Values[si].Length)),
                                chartData.CategoryNames.Select((string cn, int ci) =>
                                {
                                    var newPt = new XElement(C.pt,
                                        new XAttribute("idx", ci),
                                        new XElement(C.v, chartData.CategoryNames[ci]));
                                    return newPt;
                                })));
                    }

                    var newSer = new XElement(C.ser,
                        new XElement(C.idx, new XAttribute("val", si)),
                        new XElement(C.order, new XAttribute("val", si)),
                        new XElement(C.tx,
                            new XElement(C.v, sn)),
                        firstSeries.Elements().Where(e => e.Name == C.spPr),
                        cat,
                        new XElement(C.val,
                            new XElement(C.numLit,
                                numLit.Elements(C.formatCode),
                                new XElement(C.ptCount, new XAttribute("val", chartData.CategoryNames.Length)),
                                chartData.CategoryNames.Select((string cn, int ci) =>
                                {
                                    if (ci > chartData.Values[si].Length - 1)
                                        return null;
                                    if (chartData.Values[si][ci] == double.MinValue)
                                        return null;
                                    var newPt = new XElement(C.pt,
                                        new XAttribute("idx", ci),
                                        new XElement(C.v, chartData.Values[si][ci] != double.MinValue ?
                                            (object)chartData.Values[si][ci] : null));
                                    return newPt;
                                }))),
                        firstSeries.Elements().Where(e => e.Name != C.idx &&
                            e.Name != C.order &&
                            e.Name != C.tx &&
                            e.Name != C.cat &&
                            e.Name != C.val &&
                            e.Name != C.spPr)
                    );
                    int accentNumber = (si % 6) + 1;
                    newSer = (XElement)UpdateAccentTransform(newSer, accentNumber);
                    return newSer;
                });
            firstSeries.ReplaceWith(newSetOfSeries);
            chartPart.PutXDocument();
        }

        private static object UpdateAccentTransform(XNode node, int accentNumber)
        {
            XElement element = node as XElement;
            if (element != null)
            {
                if (element.Name == A.schemeClr && (string)element.Attribute("val") == "accent1")
                    return new XElement(A.schemeClr, new XAttribute("val", "accent" + accentNumber));

                return new XElement(element.Name,
                    element.Attributes(),
                    element.Nodes().Select(n => UpdateAccentTransform(n, accentNumber)));
            }
            return node;
        }

        private static object UnlinkSpreadsheetTransform(XNode node)
        {
            XElement element = node as XElement;
            if (element != null)
            {
                if (element.Name == C.externalData)
                    return null;

                if (element.Name == C.numFmt)
                    return new XElement(element.Name,
                        element.Attribute("formatCode"));

                if (element.Name == C.numRef)
                    return new XElement(C.numLit,
                        element.Elements(C.numCache).Elements());

                if (element.Name == C.strRef)
                    return new XElement(C.strLit,
                        element.Elements(C.strCache).Elements());

                if (element.Name == C.tx && element.Parent.Name == C.ser)
                    return new XElement(C.tx, element.Descendants(C.v));

                return new XElement(element.Name,
                    element.Attributes(),
                    element.Nodes().Select(n => UnlinkSpreadsheetTransform(n)));
            }
            return node;
        }

    }
}